'use strict';

const RECYCLER_TABLE = 'recycler';
const PICTURE_TABLE = 'picture';
const ALBUM_TABLE = 'album';

const ERR_CODE = require('../constants/errorCode');
const Service = require('egg').Service;

class RecyclerService extends Service {
  async create(userId, picIds) {
    const { ctx, app } = this;
    const values = picIds.map(item => [ userId, item, app.mysql.literals.now ]);
    const sql = `INSERT INTO ${RECYCLER_TABLE} (user_id ,pic_id, create_time) VALUES ?`;

    const conn = await app.mysql.beginTransaction(); // 初始化事务
    try {
      await conn.query(sql, values);// mysql操作
      await conn.commit(); // 提交事务

      return true;
    } catch (err) {
      await conn.rollback(); // 一定记得捕获异常后回滚事务！！
      ctx.throw(422, '新建回收照片出错', {
        errCode: ERR_CODE.Record_DBInsertFail,
        description: err.message,
      });
    }
  }

  async restore(userId, regenerantIds) {
    const { ctx, app } = this;
    // 初始化事务
    const conn = await app.mysql.beginTransaction();
    try {
      // 第一步：修改照片状态
      const defaultAlbumId = await ctx.service.album.getDefaultAlbum(userId);

      const sql1 = `UPDATE ${PICTURE_TABLE} SET status = '2' WHERE pic_id IN (SELECT pic_id FROM ${RECYCLER_TABLE} WHERE regenerant_id IN (${regenerantIds.join(',')})) AND album_id IN (SELECT album_id FROM ${ALBUM_TABLE} WHERE user_id = '?');`;
      await conn.query(sql1, userId);// 原相册还在的，直接恢复

      const sql2 = `UPDATE ${PICTURE_TABLE} SET status = '2', album_id = '?' WHERE pic_id IN (SELECT pic_id FROM ${RECYCLER_TABLE} WHERE regenerant_id in (${regenerantIds.join(',')})) AND album_id NOT IN (SELECT album_id FROM ${ALBUM_TABLE} WHERE user_id = '?');`;
      await conn.query(sql2, defaultAlbumId, userId);// 原相册不在的，放到默认相册中

      // 第二步：从回收站中删除这些照片
      const sql3 = `DELETE FROM ${RECYCLER_TABLE} WHERE regenerant_id IN (${regenerantIds.join(',')})`;
      await conn.query(sql3);
      // 提交事务;
      await conn.commit();
      return true;
    } catch (err) {
      // await conn.rollback();
      ctx.throw(422, '恢复回收站照片出错', {
        errCode: ERR_CODE.Recycler_RestoreFail,
        description: err.message,
      });
    }
  }

  async destory(regenerantIds) {
    const { ctx, app } = this;
    // 初始化事务
    const conn = await app.mysql.beginTransaction();
    try {
      // 第一步：获取照片的fileLinks
      const sql1 = `SELECT pic_id, file_link FROM ${PICTURE_TABLE} WHERE pic_id IN (SELECT pic_id 
                    FROM ${RECYCLER_TABLE} WHERE regenerant_id IN (${regenerantIds.join(',')}))`;
      const result2 = await app.mysql.query(sql1);
      const fileLinks = [];
      const picIds = [];
      result2.forEach(item => {
        fileLinks.push(item.file_link);
        picIds.push(item.pic_id);
      });
      // 第二步：在picture表中删除这些照片
      const sql2 = `DELETE FROM ${PICTURE_TABLE} WHERE pic_id IN (${picIds.join(',')})`;
      await conn.query(sql2);
      // 第三步：在回收站中删除这些回收物
      const sql3 = `DELETE FROM ${RECYCLER_TABLE} WHERE regenerant_id IN (${regenerantIds.join(',')})`;
      await conn.query(sql3, regenerantIds);
      // 第四步：在oss中删除这些照片
      await ctx.service.oss.deleteMulti(fileLinks);
      // 提交事务
      await conn.commit();
      return true;
    } catch (err) {
      await conn.rollback();
      ctx.throw(422, '永久删除回收照片出错', {
        errCode: ERR_CODE.Recycler_DestoryFail,
        description: err.message,
      });
    }
  }

  async clear(userId) {
    const { ctx, app } = this;
    // 第一步：获取要被删除的回收物ID和照片ID
    const result1 = await app.mysql.select(RECYCLER_TABLE, {
      where: { user_id: userId }, // WHERE 条件
      columns: [ 'regenerant_id', 'pic_id' ],
    });
    const regenerantIds = [];
    const picIds = [];
    result1.forEach(item => {
      regenerantIds.push(item.regenerant_id);
      picIds.push(item.pic_id);
    });

    if (picIds.length === 0) {
      return true;
    }

    // 初始化事务
    const conn = await app.mysql.beginTransaction();

    try {
      // 第二步：获取照片的fileLinks
      const sql1 = `SELECT file_link FROM ${PICTURE_TABLE} WHERE pic_id IN (${picIds.join(',')})`;
      const result2 = await app.mysql.query(sql1);
      const fileLinks = result2.map(item => item.file_link);

      // 第三步：在picture中删除这些照片
      const sql2 = `DELETE FROM ${PICTURE_TABLE} WHERE pic_id IN (${picIds.join(',')})`;
      await conn.query(sql2);

      // 第四步：在回收站中删除这些回收物
      const sql3 = `DELETE FROM ${RECYCLER_TABLE} WHERE user_id = '${userId}';`;
      await conn.query(sql3);

      // 第五步：在oss中删除这些照片
      await ctx.service.oss.deleteMulti(fileLinks);
      // 提交事务
      await conn.commit();

      return true;
    } catch (err) {
      console.log(err);
      await conn.rollback();
      ctx.throw(422, '清空回收站出错', {
        errCode: ERR_CODE.Recycler_ClearFail,
        description: err.message,
      });
    }
  }

  async index(userId, page, size) {
    console.log('获取相册内照片', userId, page, size);

    const { app } = this;
    const sql1 = `SELECT l.regenerant_id AS 'key', l.pic_id, m.album_id, m.name AS album_name, r.file_name, r.file_link, l.create_time FROM ${RECYCLER_TABLE} AS l LEFT JOIN (${PICTURE_TABLE} AS r LEFT JOIN ${ALBUM_TABLE} AS m ON r.album_id = m.album_id) ON l.pic_id = r.pic_id WHERE l.user_id = '${userId}' ORDER BY l.create_time DESC, l.regenerant_id DESC LIMIT ${size} OFFSET ${(page - 1) * size} ;`;
    const result1 = await app.mysql.query(sql1);
    const sql2 = `SELECT count(*) AS total from ${RECYCLER_TABLE} WHERE user_id = '${userId}';`;
    const result2 = await app.mysql.query(sql2);
    return { list: result1, total: result2[0].total };
  }
}

module.exports = RecyclerService;
